from typing import List, Optional, Dict, Any, Tuple
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func, distinct
from sqlalchemy.orm import selectinload

from app.models.models import Class, School
from app.schemas.schemas import ClassCreate, ClassFilters, DynamicClassFilters


async def get_class_filters(db: AsyncSession, school_id: int) -> ClassFilters:
    """获取班级筛选条件（旧版，保留兼容性）"""
    # 获取学年列表
    academic_years_result = await db.execute(
        select(distinct(Class.academic_year_name))
        .filter(Class.school_id == school_id)
        .order_by(Class.academic_year_name)
    )
    academic_years = [year[0] for year in academic_years_result.all()]

    # 获取学期列表
    terms_result = await db.execute(
        select(distinct(Class.term_code))
        .filter(Class.school_id == school_id)
        .order_by(Class.term_code)
    )
    terms = [term[0] for term in terms_result.all()]

    # 获取校区列表
    campuses_result = await db.execute(
        select(distinct(Class.campus_name))
        .filter(Class.school_id == school_id)
        .order_by(Class.campus_name)
    )
    campuses = [campus[0] for campus in campuses_result.all()]

    # 获取学院列表
    colleges_result = await db.execute(
        select(distinct(Class.college_name))
        .filter(Class.school_id == school_id)
        .order_by(Class.college_name)
    )
    colleges = [college[0] for college in colleges_result.all()]

    # 获取年级列表
    grades_result = await db.execute(
        select(distinct(Class.grade_name))
        .filter(Class.school_id == school_id)
        .order_by(Class.grade_name)
    )
    grades = [grade[0] for grade in grades_result.all()]

    # 获取专业列表
    majors_result = await db.execute(
        select(distinct(Class.major_name))
        .filter(Class.school_id == school_id)
        .order_by(Class.major_name)
    )
    majors = [major[0] for major in majors_result.all()]

    # 获取培养层次列表
    education_levels_result = await db.execute(
        select(distinct(Class.education_level))
        .filter(Class.school_id == school_id)
        .order_by(Class.education_level)
    )
    education_levels = [level[0] for level in education_levels_result.all()]

    return ClassFilters(
        academic_years=academic_years,
        terms=terms,
        campuses=campuses,
        colleges=colleges,
        grades=grades,
        majors=majors,
        education_levels=education_levels
    )


async def get_dynamic_class_filters(
    db: AsyncSession,
    school_id: int,
    academic_year: Optional[str] = None,
    term: Optional[str] = None,
    campus: Optional[str] = None,
    college: Optional[str] = None,
    grade: Optional[str] = None,
    major: Optional[str] = None,
    education_level: Optional[str] = None
) -> Dict[str, Any]:
    """
    获取动态班级筛选条件（支持联动筛选）
    
    根据已选的筛选条件，返回其他维度的可选值及对应的班级数量
    不传入的参数认为用户未选中
    """
    # 构建基础查询条件
    base_filters = [Class.school_id == school_id]
    
    # 收集已应用的筛选条件
    applied_filters = {"school_id": school_id}
    
    if academic_year:
        base_filters.append(Class.academic_year_name == academic_year)
        applied_filters["academic_year"] = academic_year
    if term:
        base_filters.append(Class.term_code == term)
        applied_filters["term"] = term
    if campus:
        base_filters.append(Class.campus_name == campus)
        applied_filters["campus"] = campus
    if college:
        base_filters.append(Class.college_name == college)
        applied_filters["college"] = college
    if grade:
        base_filters.append(Class.grade_name == grade)
        applied_filters["grade"] = grade
    if major:
        base_filters.append(Class.major_name == major)
        applied_filters["major"] = major
    if education_level:
        base_filters.append(Class.education_level == education_level)
        applied_filters["education_level"] = education_level
    
    # 查询总班级数
    total_query = select(func.count(Class.id)).filter(*base_filters)
    total_classes = await db.scalar(total_query) or 0
    
    # 查询符合条件的班级列表（简化信息）
    classes_query = (
        select(Class.class_name, Class.class_code)
        .filter(*base_filters)
        .order_by(Class.class_name)
    )
    classes_result = await db.execute(classes_query)
    classes_data = [
        {"class_name": row[0], "class_code": row[1]}
        for row in classes_result.all()
    ]
    
    # 定义需要查询的维度（field, title, column, current_value, text_func）
    dimensions = [
        ("academic_years", "学年", Class.academic_year_name, academic_year, None),
        ("terms", "学期", Class.term_code, term, {"1": "第一学期", "2": "第二学期", "3": "第三学期"}),
        ("campuses", "校区", Class.campus_name, campus, None),
        ("colleges", "学院", Class.college_name, college, None),
        ("grades", "年级", Class.grade_name, grade, lambda v: f"{v}级"),
        ("majors", "专业", Class.major_name, major, None),
        ("education_levels", "培养层次", Class.education_level, education_level, None)
    ]
    
    filters_list = []
    
    # 为每个维度查询可选值和计数
    for field, title, column, current_value, text_func in dimensions:
        # 构建该维度的查询（排除当前维度的筛选条件）
        dimension_filters = [Class.school_id == school_id]
        if academic_year and column != Class.academic_year_name:
            dimension_filters.append(Class.academic_year_name == academic_year)
        if term and column != Class.term_code:
            dimension_filters.append(Class.term_code == term)
        if campus and column != Class.campus_name:
            dimension_filters.append(Class.campus_name == campus)
        if college and column != Class.college_name:
            dimension_filters.append(Class.college_name == college)
        if grade and column != Class.grade_name:
            dimension_filters.append(Class.grade_name == grade)
        if major and column != Class.major_name:
            dimension_filters.append(Class.major_name == major)
        if education_level and column != Class.education_level:
            dimension_filters.append(Class.education_level == education_level)
        
        # 查询该维度的所有可选值及其计数
        query = (
            select(column, func.count(Class.id).label('count'))
            .filter(*dimension_filters)
            .group_by(column)
            .order_by(column)
        )
        
        result = await db.execute(query)
        rows = result.all()
        
        # 构建 FilterOption 列表
        options = []
        for value, count in rows:
            if value:  # 排除空值
                # 生成显示文本
                if isinstance(text_func, dict):
                    text = text_func.get(value, value)
                elif callable(text_func):
                    text = text_func(value)
                else:
                    text = value
                
                options.append({
                    "value": value,
                    "text": text,
                    "count": count
                })
        
        # 添加到维度列表
        filters_list.append({
            "field": field,
            "title": title,
            "options": options
        })
    
    return {
        "filters": filters_list,
        "classes_data": classes_data,
        "total_classes": total_classes,
        "applied_filters": applied_filters
    }


async def get_classes(
    db: AsyncSession,
    school_id: int,
    academic_year: Optional[str] = None,
    term: Optional[str] = None,
    campus: Optional[str] = None,
    college: Optional[str] = None,
    grade: Optional[str] = None,
    major: Optional[str] = None,
    education_level: Optional[str] = None,
    page: int = 1,
    size: int = 10
) -> Tuple[List[Class], int]:
    """获取班级列表"""
    # 构建查询
    query = select(Class).filter(Class.school_id == school_id)

    # 添加筛选条件
    if academic_year:
        query = query.filter(Class.academic_year_name == academic_year)
    if term:
        query = query.filter(Class.term_code == term)
    if campus:
        query = query.filter(Class.campus_name == campus)
    if college:
        query = query.filter(Class.college_name == college)
    if grade:
        query = query.filter(Class.grade_name == grade)
    if major:
        query = query.filter(Class.major_name == major)
    if education_level:
        query = query.filter(Class.education_level == education_level)

    # 计算总数 - 直接查询，不使用子查询
    count_query = select(func.count(Class.id)).select_from(Class).filter(Class.school_id == school_id)
    
    # 添加筛选条件到总数查询
    if academic_year:
        count_query = count_query.filter(Class.academic_year_name == academic_year)
    if term:
        count_query = count_query.filter(Class.term_code == term)
    if campus:
        count_query = count_query.filter(Class.campus_name == campus)
    if college:
        count_query = count_query.filter(Class.college_name == college)
    if grade:
        count_query = count_query.filter(Class.grade_name == grade)
    if major:
        count_query = count_query.filter(Class.major_name == major)
    if education_level:
        count_query = count_query.filter(Class.education_level == education_level)
    
    total = await db.scalar(count_query)
    
    # 构建分页查询
    query = select(Class).filter(Class.school_id == school_id)
    
    # 添加筛选条件
    if academic_year:
        query = query.filter(Class.academic_year_name == academic_year)
    if term:
        query = query.filter(Class.term_code == term)
    if campus:
        query = query.filter(Class.campus_name == campus)
    if college:
        query = query.filter(Class.college_name == college)
    if grade:
        query = query.filter(Class.grade_name == grade)
    if major:
        query = query.filter(Class.major_name == major)
    if education_level:
        query = query.filter(Class.education_level == education_level)
    
    # 分页
    query = query.order_by(Class.id).offset((page - 1) * size).limit(size)

    # 执行查询
    result = await db.execute(query)
    classes = result.scalars().all()

    return classes, total


async def get_class_by_code(db: AsyncSession, class_code: str) -> Optional[Class]:
    """根据班级编号获取班级"""
    result = await db.execute(
        select(Class).filter(Class.class_code == class_code)
    )
    return result.scalars().first()


async def get_all_classes(db: AsyncSession, school_id: Optional[int] = None) -> List[Class]:
    """获取全部班级列表（可选按学校ID过滤，不分页）"""
    query = select(Class)
    if school_id is not None:
        query = query.filter(Class.school_id == school_id)

    result = await db.execute(query.order_by(Class.id))
    classes = result.scalars().all()
    return classes


async def create_class(db: AsyncSession, class_obj: ClassCreate) -> Class:
    """创建或更新班级：
    若存在 (school_id, class_code, term_code, campus_id) 完全相同的记录，则更新该记录；否则插入新记录。
    """
    # 先查询是否存在相同唯一键的记录
    result = await db.execute(
        select(Class).filter(
            (Class.school_id == class_obj.school_id) &
            (Class.class_code == class_obj.class_code) &
            (Class.term_code == class_obj.term_code) &
            (Class.campus_id == class_obj.campus_id)
        )
    )
    exist = result.scalars().first()

    data = class_obj.model_dump()

    if exist:
        # 更新已存在记录的字段
        for k, v in data.items():
            setattr(exist, k, v)
        await db.commit()
        await db.refresh(exist)
        return exist
    else:
        # 插入新记录
        db_class = Class(**data)
        db.add(db_class)
        await db.commit()
        await db.refresh(db_class)
        return db_class
